﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace Project.dalSql
{
    public class dalAccess
    {
        private dalTransaction transection = null;

        public dalAccess(dalTransaction tran)
        {
            if (tran == null)
                transection = new dalTransaction();
            else
                transection = tran;
        }

        public DataSet Quary(string strSql, DataSet ds, string[] strTableName)
        {
            DataSet ds1 = new DataSet();
            try
            {
                if (transection.myTrans != null)
                {
                    dalSqlHelper.FillDataset(transection.myTrans, CommandType.Text, strSql, ds, strTableName);
                }
                else
                {
                    dalSqlHelper.FillDataset(dalSqlHelper.CONN_STRING_NON_DTC, CommandType.Text, strSql, ds, strTableName);

                }

                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public DataSet Quary(string strSql, DataSet ds, string[] strTableName, params SqlParameter[] Params)
        {
            DataSet ds1 = new DataSet();
            try
            {
                if (transection.myTrans != null)
                {
                    dalSqlHelper.FillDataset(transection.myTrans, CommandType.Text, strSql, ds, strTableName, Params);
                }
                else
                {
                    dalSqlHelper.FillDataset(dalSqlHelper.CONN_STRING_NON_DTC, CommandType.Text, strSql, ds, strTableName, Params);

                }

                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public SqlDataReader Quary(string strSql)
        {
            SqlDataReader reader = null;
            try
            {
                if (transection.myTrans != null)
                {
                    reader = dalSqlHelper.ExecuteReader(transection.myTrans, CommandType.Text, strSql);
                }
                else
                {
                    reader = dalSqlHelper.ExecuteReader(dalSqlHelper.CONN_STRING_NON_DTC, CommandType.Text, strSql);

                }

                return reader;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }


        public int Insert(string strSql)
        {
            int iRet = -1;
            try
            {
                if (transection.myTrans != null)
                {
                    iRet = dalSqlHelper.ExecuteNonQuery(transection.myTrans, CommandType.Text, strSql);
                }
                else
                    iRet = dalSqlHelper.ExecuteNonQuery(dalSqlHelper.CONN_STRING_NON_DTC, CommandType.Text, strSql);

                if (iRet == 0)
                    return 1;

                return 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                throw ex;
            }
        }

        public int Updata(string strSql)
        {
            int iRet = -1;
            try
            {
                if (transection.myTrans != null)
                {
                    iRet = dalSqlHelper.ExecuteNonQuery(transection.myTrans, CommandType.Text, strSql);
                }
                else
                    iRet = dalSqlHelper.ExecuteNonQuery(dalSqlHelper.CONN_STRING_NON_DTC, CommandType.Text, strSql);

                if (iRet == 0)
                    return 1;
                return 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                throw ex;
            }
        }

        public int Delete(string strSql)
        {
            int iRet = -1;
            try
            {
                if (transection.myTrans != null)
                {
                    iRet = dalSqlHelper.ExecuteNonQuery(transection.myTrans, CommandType.Text, strSql);
                }
                else
                    iRet = dalSqlHelper.ExecuteNonQuery(dalSqlHelper.CONN_STRING_NON_DTC, CommandType.Text, strSql);

                if (iRet == 0)
                    return 1;

                return 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                throw ex;
            }
        }


        public DataSet StoredProcedure(string strStorageName, DataSet ds, string[] strTableName)
        {
            try
            {
                if (transection.myTrans != null)
                {
                    dalSqlHelper.FillDataset(transection.myTrans, CommandType.StoredProcedure, strStorageName, ds, strTableName);
                }
                else
                {
                    dalSqlHelper.FillDataset(dalSqlHelper.CONN_STRING_NON_DTC, CommandType.StoredProcedure, strStorageName, ds, strTableName);

                }

                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void HandleTable(string strInsertSql, string strDeleteSql, string strUpdataSql, DataSet ds, string strTableName)
        {
            System.Data.SqlClient.SqlCommand InsertCmd = null;
            System.Data.SqlClient.SqlCommand DeleteCmd = null;
            System.Data.SqlClient.SqlCommand UpdataCmd = null;

            if (transection.myTrans != null)
            {
                if (strInsertSql != "")
                    InsertCmd = new SqlCommand(strInsertSql, transection.conn, transection.myTrans);
                if (strDeleteSql != "")
                    DeleteCmd = new SqlCommand(strDeleteSql, transection.conn, transection.myTrans);
                if (strUpdataSql != "")
                    UpdataCmd = new SqlCommand(strUpdataSql, transection.conn, transection.myTrans);
            }
            else
            {
                if (strInsertSql != "")
                    InsertCmd = new SqlCommand(strInsertSql, transection.conn);
                if (strDeleteSql != "")
                    DeleteCmd = new SqlCommand(strDeleteSql, transection.conn);
                if (strUpdataSql != "")
                    UpdataCmd = new SqlCommand(strUpdataSql, transection.conn);
            }

            try
            {
                dalSqlHelper.UpdateDataset(InsertCmd, DeleteCmd, UpdataCmd, ds, strTableName);
            }
            catch (System.Data.DBConcurrencyException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public int InsertTable(string strInsertSql, DataSet ds, string strTableName)
        {
            System.Data.SqlClient.SqlCommand Insertcmd = null;

            if (transection.myTrans != null)
                Insertcmd = new SqlCommand(strInsertSql, transection.conn, transection.myTrans);
            else
                Insertcmd = new SqlCommand(strInsertSql, transection.conn);
            try
            {
                dalSqlHelper.UpdateDataset(Insertcmd, Insertcmd, Insertcmd, ds, strTableName);
                return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
    }
}
